﻿Imports System.Data.SqlClient
Imports System.Data
Public Class ViewQuestions
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Request.Params("Question") = "" Then Response.Redirect("Books.aspx")
        If Request.Browser.IsMobileDevice Then
            Response.Redirect("~/ViewQuestion_mobile.aspx?Question=" + Request.Params("Question") + "&Book_FK=" + Request.Params("ID"))
        End If
        If IsPostBack = False Then
            Comments.GetSolutions(Request.Params("Question"), CommentsLtr)

            'Check if user has voted this question
            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
            Dim hasVotedcmd As New SqlCommand("SELECT * FROM Question_Votes WHERE Question_FK = @f1 AND Voter = @f2", conn)
            conn.Open()
            hasVotedcmd.CommandType = CommandType.Text
            hasVotedcmd.Parameters.AddWithValue("@f1", Request.Params("Question"))
            hasVotedcmd.Parameters.AddWithValue("@f2", User.Identity.Name)

            'Find out if there any records at all (returned)
            Dim drVotes As SqlDataReader = hasVotedcmd.ExecuteReader()
            'this shows if any data is returned
            If drVotes.HasRows Then
                btn_VoteQuestion.Enabled = False
                btn_VoteQuestion.Text = "Voted"
            Else
                btn_VoteQuestion.Enabled = True
                btn_VoteQuestion.Text = "Vote"
            End If
            conn.Close()
        End If
    End Sub

    Protected Sub SqlDataSource2_Selected(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource2.Selected
        'If datasource is empty
        If (e.AffectedRows < 1) Then
            lbl_Questions.Text = "No questions found"
        Else
            lbl_Questions.Text = ""
        End If
    End Sub

    Protected Sub SqlDataSource3_Selected(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource3.Selected
        'If datasource is empty
        If (e.AffectedRows < 1) Then
            lbl_Solution.Text = "Solution not provided"
        Else
            lbl_Solution.Text = ""
        End If
    End Sub

    Private Function SafeSqlLiteral(inputSQL As String) As String
        Return inputSQL.Replace("'", "''")
    End Function

    Protected Sub btn_Solution_Click(sender As Object, e As EventArgs) Handles btn_Solution.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("INSERT INTO [Solutions] ([SolutionName], [Title], [Questions_FK], [SolutionBody]) VALUES (@f1, @f2, @f3, @f4)", conn)
        Dim SqlString As String = ""
        conn.Open()
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", SafeSqlLiteral(tb_Title.Text))
        cmd.Parameters.AddWithValue("@f3", Request.Params("Question"))
        cmd.Parameters.AddWithValue("@f4", SafeSqlLiteral(tb_Comment.Text))
        cmd.ExecuteNonQuery()
        conn.Close()
        conn.Dispose()
        Comments.GetSolutions(Request.Params("Question"), CommentsLtr)
        getTotalSolutions()
        tb_Comment.Text = ""
        tb_Title.Text = ""
    End Sub

    Protected Function getTotalSolutions() As String
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("SELECT * FROM TotalSolutionsPerQuestion WHERE ID = @f1", conn)
        conn.Open()
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("Question"))
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        Dim retValue As Integer = dr("TotalSolutions")
        If retValue <> 1 Then
            Return dr("TotalSolutions") & " Solutions"
        Else
            Return dr("TotalSolutions") & " Solution"
        End If
        conn.Close()
        conn.Dispose()
    End Function

    Protected Sub btn_VoteQuestion_Click(sender As Object, e As EventArgs) Handles btn_VoteQuestion.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim addVotecmd As New SqlCommand("INSERT INTO Question_Votes (Question_FK, Voter) VALUES (@f1, @f2)", conn)
        conn.Open()
        addVotecmd.CommandType = CommandType.Text
        addVotecmd.Parameters.AddWithValue("@f1", Request.Params("Question"))
        addVotecmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        addVotecmd.ExecuteNonQuery()
        conn.Close()
        conn.Dispose()
        addVotecmd.Dispose()
        'Update the repeater to display the vote
        rpt_Votes.DataBind()
        'hide button
        btn_VoteQuestion.Enabled = False
        btn_VoteQuestion.Text = "Voted"
    End Sub
End Class